﻿// ==============================================================================================
// Author:		Nicolas Welitzki
// Create date: 2013-8-1
// ==============================================================================================

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Xml.Linq;
using System.Collections;
using System.Diagnostics;
using Microsoft.Data.Schema.ScriptDom.Sql;
using Microsoft.Data.Schema.ScriptDom;


namespace StaticAnalyzingApplication
{
    /// <summary>
    /// XML parser for dtsx files (ETL packages)
    /// </summary>
    /// <remarks>
    /// Check naming conventions with method: checkComponent
    /// Check DataAccessMode FastSelection of a OLE DB Source Component with method: checkOleDbSource
    /// Check using Views for OLE DDB Source Component with method: checkViewUse
    /// Check fast load options for OLE DB Destination Component with method: checkOleDbDestination
    /// Check SQL-statement on 'Select *' with method: checkColumnUse
    /// </remarks>
    public partial class Parser : Form
    {
        /// <summary>
        /// Array for the output error
        /// </summary>
        ArrayList resultArrayList = new ArrayList();

        /// <summary>
        /// Number of created files 
        /// </summary>
        int fileNr = 1;

        /// <summary>
        /// Deposit of the English name convention
        /// </summary>
        protected Dictionary<string, string> conventionDictEnglish = new Dictionary<string, string>()
	    {
            // Task
	        {"For Loop Container","FLC"},
            {"Foreach Loop Container","FELC"},
            {"Sequence Container","SEQC"},
            {"ActiveX Script","AXS"},
            {"Analysis Services Execute DDL","ASE"},
            {"Analysis Services Processing","ASP"},
            {"Bulk Insert","BLK"},
            {"Data Flow","DFT"},
            {"Data Mining Query","DMQ"},
            {"Execute DTS 2000 Package","EDPT"},
            {"Execute Package","EPT"},
            {"Execute Process","EPR"},
            {"Execute SQL","SQL"},
            {"File System","FSYS"},
            {"FTP","FTP"},
            {"Message Queue","MSMQ"},
            {"Script","SCR"},
            {"Send Mail","SMT"},
            {"Transfer Database","TDB"},
            {"Transfer Error Messages","TEM"},
            {"Transfer Jobs","TJT"},
            {"Transfer Logins","TLT"},
            {"Transfer Master Stored Procedures","TSP"},
            {"Transfer SQL Server Objects","TSO"},
            {"Web Service","WST"},
            {"WMI Data Reader","WMID"},
            {"WMI Event Watcher","WMIE"},
            {"XML","XML"},
      
            //ComponentPrefix
            {"DataReader Source","DR_SRC"},
            {"Excel Source","EX_SRC"},
            {"Flat File Source","FF_SRC"},
            {"OLE DB Source","OLE_SRC"},
            {"Raw File Source","RF_SRC"},
            {"XML Source","XML_SRC"},
            {"Aggregate","AGG"},
            {"Audit","AUD"},
            {"Character Map","CHM"},
            {"Conditional Split","CSPL"},
            {"Copy Column","CPYC"},
            {"Data Conversion","DCNV"},
            //{"Data Mining Query","DMQ"},
            {"Derived Column","DER"},
            {"Export Column","EXPC"},
            {"Fuzzy Grouping","FZG"},
            {"Fuzzy Lookup","FZL"},
            {"Import Column","IMPC"},
            {"Lookup","LKP"},
            {"Merge","MRG"},
            {"Merge Join","MRGJ"},
            {"Multicast","MLT"},
            {"OLE DB Command","CMD"},
            {"Percentage Sampling","PSMP"},
            {"Pivot","PVT"},
            {"Row Count","CNT"},
            {"Row Sampling","RSMP"},
            {"Script Component","SCR"},
            {"Slowly Changing Dimension","SCD"},
            {"Sort","SRT"},
            {"Term Extraction","TEX"},
            {"Term Lookup","TEL"},
            {"Union All","ALL"},
            {"Unpivot","UPVT"},
            {"Data Mining Model Training","DMMT_DST"},
            {"DataReader Destination","DR_DST"},
            {"Dimension Processing","DP_DST"},
            {"Excel Destination","EX_DST"},
            {"Flat File Destination","FF_DST"},
            {"OLE DB Destination","OLE_DST"},
            {"Partition Processing","PP_DST"},
            {"Raw File Destination","RF_DST"},
            {"Recordset Destination","RS_DST"},
            {"SQL Server Destination","SS_DST"},
            {"SQL Server Mobile Destination","SSM_DST"}
	    };

        /// <summary>
        /// Deposit of the German name convention
        /// </summary>
        protected Dictionary<string, string> conventionDictGerman = new Dictionary<string, string>()
	    {
            // Task
            {"For Loop Container","FLC"},
            {"Foreach Loop Container","FELC"},
            {"Sequence Container","SEQC"},
            {"ActiveX Script","AXS"},
            {"Analysis Services Execute DDL","ASE"},
            {"Analysis Services Processing","ASP"},
            {"Bulk Insert","BLK"},
            {"Datenfluss","DFT"},
            {"Data Mining Query","DMQ"},
            {"Execute DTS 2000 Package","EDPT"},
            {"Execute Package","EPT"},
            {"Execute Process","EPR"},
            {"Execute SQL","SQL"},
            {"File System","FSYS"},
            {"FTP","FTP"},
            {"Message Queue","MSMQ"},
            {"Skript","SCR"},
            {"Send Mail","SMT"},
            {"Transfer Database","TDB"},
            {"Transfer Error Messages","TEM"},
            {"Transfer Jobs","TJT"},
            {"Transfer Logins","TLT"},
            {"Transfer Master Stored Procedures","TSP"},
            {"Transfer SQL Server Objects","TSO"},
            {"Web Service","WST"},
            {"WMI Data Reader","WMID"},
            {"WMI Event Watcher","WMIE"},
            {"XML","XML"},
      
            //ComponentPrefix
            {"DataReader Source","DR_SRC"},
            {"Excel Source","EX_SRC"},
            {"Flat File Source","FF_SRC"},
            {"OLE DB-Quelle","OLE_SRC"},
            {"Raw File Source","RF_SRC"},
            {"XML Source","XML_SRC"},
            {"Aggregate","AGG"},
            {"Audit","AUD"},
            {"Character Map","CHM"},
            {"Conditional Split","CSPL"},
            {"Copy Column","CPYC"},
            {"Data Conversion","DCNV"},
            //{"Data Mining Query","DMQ"},
            {"Abgeleitete Spalte","DER"},
            {"Export Column","EXPC"},
            {"Fuzzygruppierung","FZG"},
            {"Fuzzy Lookup","FZL"},
            {"Import Column","IMPC"},
            {"Suche","LKP"},
            {"Merge","MRG"},
            {"Merge Join","MRGJ"},
            {"Multicast","MLT"},
            {"OLE DB Command","CMD"},
            {"Percentage Sampling","PSMP"},
            {"Pivot","PVT"},
            {"Zeilenanzahl","CNT"},
            {"Row Sampling","RSMP"},
            {"Script Component","SCR"},
            {"Slowly Changing Dimension","SCD"},
            {"Sort","SRT"},
            {"Term Extraction","TEX"},
            {"Term Lookup","TEL"},
            {"Union All","ALL"},
            {"Unpivot","UPVT"},
            {"Data Mining Model Training","DMMT_DST"},
            {"DataReader Destination","DR_DST"},
            {"Dimension Processing","DP_DST"},
            {"Excel Destination","EX_DST"},
            {"Flat File Destination","FF_DST"},
            {"OLE DB-Ziel","OLE_DST"},
            {"Partition Processing","PP_DST"},
            {"Raw File Destination","RF_DST"},
            {"Recordset Destination","RS_DST"},
            {"SQL Server Destination","SS_DST"},
            {"SQL Server Mobile Destination","SSM_DST"}
	    };

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="initialPath">Path where the ETL package is</param>
        /// <param name="logFileFullPath">Path for the LogFile</param>
        public Parser(string initialPath, string logFileFullPath)
        {
            InitializeComponent();
            this.folderBrowserDialog1.SelectedPath = initialPath;
            this.saveFileDialog1.FileName = logFileFullPath;

            //this.folderBrowserDialog1.SelectedPath = "C:\\Users\\nwelit\\Documents\\Visual Studio 2010\\Projects\\Integration Services Project1\\Integration Services Project1";
            //this.button2_Click(new object(), new EventArgs());
        }

        /// <summary>
        /// Constructor
        /// </summary>
        public Parser()
            : this("", "")
        {
        }

        /// <summary>
        /// Upload the directory to the TreeView
        /// </summary>
        private void button2_Click(object sender, EventArgs e)
        {
            this.treeView1.Nodes.Clear();
            this.resultArrayList = new ArrayList();
            this.label1.Text = "Files:";
            
            DialogResult result = this.folderBrowserDialog1.ShowDialog();

            if (result == DialogResult.OK)
            {
                string path = folderBrowserDialog1.SelectedPath;
                DirectoryInfo dir = new DirectoryInfo(path);

                try
                {
                    foreach (FileInfo file in dir.GetFiles())
                    {
                        if (File.Exists(file.FullName.ToString()) && file.Extension.ToLower().Equals(".dtsx"))
                        {
                            TreeNode node = new TreeNode();
                            node.Text = file.ToString();
                            node.Tag = file.FullName.ToString();
                            this.treeView1.Nodes.Add(node);
                        }
                    }

                }
                catch (Exception ex)
                {
                    MessageBox.Show("Failed to open the directory: " + ex.Message, this.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    this.treeView1.Nodes.Clear();
                    return;
                }

                this.startAnalyzing();
             }
        }

        /// <summary>
        ///  Searches recursively in an XML file by Tags
        /// </summary>
        /// <param name="elm">XElement to be searched</param>
        /// <param name="fileName">File name</param>
        private void scan(XElement elm, string fileName)
        {
            foreach (XElement subElm in elm.Elements())
            {
                if (subElm.Name == "pipeline")
                {
                    foreach (XElement com in subElm.Elements("components"))
                    {
                        this.scanComponent(com, fileName);
                    }
                }
                else
                {
                    this.scan(subElm, fileName);
                }
            }

        }

        /// <summary>
        ///  Browse a XML file recursively by Tags and perform a validation
        /// </summary>
        /// <param name="com">XElement to be searched</param>
        /// <param name="fileName">File name</param>
        private void scanComponent(XElement com, string fileName)
        {
            foreach(XElement component in com.Elements() )
            {
                string name = component.Name.ToString();

                if (name == "component")
                {
                    string type = component.Attribute("contactInfo").Value.ToString();
                    string componentClassID = component.Attribute("componentClassID").Value.ToString();

                    if (type.Split(new Char[] { ';' }).Length > 0)
                    {
                        string typeName = type.Split(new Char[] { ';' }).First();

                        if (typeName.Length > 40)
                            continue;

                        string realName = component.Attribute("name").Value;

                        // Check naming convention
                        this.checkComponent(typeName, realName, fileName, componentClassID);

                        //if (typeName == "OLE DB Source" || typeName == "OLE DB-Quelle")
                        if (componentClassID == "{165A526D-D5DE-47FF-96A6-F8274C19826B}") // OLE DB Source Component
                        {
                            if (this.checkOleDbSource(component, fileName))
                                this.checkViewUse(component, fileName);
                        }

                            //else if (typeName == "OLE DB Destination" || typeName == "OLE DB-Ziel")
                        else if (componentClassID == "{4ADA7EAA-136C-4215-8098-D7A7C27FC0D1}") // OLE DB Destination Component
                            this.checkOleDbDestination(component, fileName);

                        if (componentClassID == "{671046B0-AA63-4C9F-90E4-C06E0B710CE3}" && typeName != "") // Lookup Component 
                        {
                            checkColumnUse(component, fileName);
                        }
                    }
                }
                else
                    this.scanComponent(component, fileName);
            }
        }

        /// <summary>
        ///  Start iterating over TreeView nodes and validation of ETL packages
        /// </summary>
        private void startAnalyzing()
        {
            this.resultArrayList = new ArrayList();

            foreach (TreeNode n in this.treeView1.Nodes)
            {
                string fileName = n.Text;
                string fullPath = n.Tag.ToString();

                XElement xml;

                try
                {
                    xml = System.Xml.Linq.XElement.Load(fullPath);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("The file could not be opened. The XElement is missing\n" + fileName, this.Name, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    resultArrayList.AddRange(new object[] { new object[] { fileName, fullPath.ToString(), ex.Message.ToString(), fileName } });
                    continue;
                }

                XNamespace a = xml.Attributes().First().Value;
                string prefix = xml.GetPrefixOfNamespace(a);

                if (xml.Elements(a + "Executables").Any() == true)
                {
                    try
                    {
                        foreach (XElement elementRoot in xml.Element(a + "Executables").Element(a + "Executable").Elements())
                        {
                            this.scan(elementRoot, fileName);
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("A non-evaluable XML node could not be opened.\n" + xml.ToString(), this.Name, MessageBoxButtons.OK, MessageBoxIcon.Information);
                        resultArrayList.AddRange(new object[] { new object[] { prefix, a, ex.Message.ToString(), fileName } });
                    }
                }
            }

            if (treeView1.Nodes.Count > 0)
            {
                this.label1.Text = "Files with '" + this.resultArrayList.Count + "' errors";
                this.showResult();
            }
            else
                MessageBox.Show("No dtsx files found", this.Name, MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        /// <summary>
        ///  Show results as Message Box
        /// </summary>
        private void showResult()
        {
            string result = "";

            if (this.resultArrayList.Count > 0)
            {
                result = "Result:\n";
                string t = "\n";

                foreach (Object[] erg in this.resultArrayList)
                {
                    string type = "Typ: " + (String)erg[0];
                    string name = "Name: " + (String)erg[1];
                    string text = (String)erg[2];
                    string fileName = "File: " + (String)erg[3];

                    result = result + type + t + name + t + text + t + fileName + "\n\n";
                }

            }
            else
                result = "You're good. There are no mistakes.";

            MessageBox.Show(result , this.Name, MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

        /// <summary>
        /// Testing for Data access mode quick selection
        /// </summary>
        /// <param name="element">XElement to be tested</param>
        /// <param name="fileName">File name</param>
        private Boolean checkOleDbSource(XElement element, string fileName)
        {
            Boolean isFault = true;
            string type = element.Attribute("contactInfo").Value.Split(new Char[] { ';' }).First();
            string componentClassID = element.Attribute("componentClassID").Value.ToString();
            string name = element.Attribute("name").Value;

            string openRowSet = "";
            string sqlCommand = "";

            IEnumerable<XElement> OpenRowset = from f in element.Elements("properties").Elements()
                                               where (string)f.Attribute("name").Value.ToLower() == "OpenRowset".ToLower()
                                               select f;

            foreach (XElement el in OpenRowset)
            {
                openRowSet = el.Value.ToString();
            }

            IEnumerable<XElement> SQLCommand = from f in element.Elements("properties").Elements()
                                               where (string)f.Attribute("name").Value.ToLower() == "SqlCommand".ToLower()
                                               select f;

            foreach (XElement el in SQLCommand)
            {
                sqlCommand = el.Value.ToString();
            }

            if (openRowSet != "" && sqlCommand == "")
                isFault = true;
            else
                isFault = false;

            if (isFault)
                resultArrayList.AddRange(new object[] { new object[] { type, name, "Error: Data access mode quick selection" , fileName}});

            return !isFault;
        }

        /// <summary>
        ///  Check whether a view is used in the database select - Determination of the query without evaluating
        /// </summary>
        /// <param name="element">XElement to be tested</param>
        /// <param name="fileName">File name</param>
        private Boolean checkViewUse(XElement element, string fileName)
        {
            Boolean isFault = true;
            string type = element.Attribute("contactInfo").Value.Split(new Char[] { ';' }).First();
            string componentClassID = element.Attribute("componentClassID").Value.ToString();
            string name = element.Attribute("name").Value;

            string sqlCommand = "";

            IEnumerable<XElement> SQLCommand = from f in element.Elements("properties").Elements()
                                               where (string)f.Attribute("name").Value.ToLower() == "SqlCommand".ToLower()
                                               select f;

            foreach (XElement el in SQLCommand)
            {
                sqlCommand = el.Value.ToString();
            }

            if (sqlCommand != "")
            {
                TSql100Parser parser = new TSql100Parser(false);
                TextReader rd = new StringReader(sqlCommand);
                IList<ParseError> errors;
                IScriptFragment fragments = parser.Parse(rd, out errors);

                // Extract the query assuming it is a SelectStatement
                QueryExpression query = ((fragments as TSqlScript).Batches[0].Statements[0] as SelectStatement).QueryExpression;

                //// Get the field list of selected columns
                //List<string> fieldList = new List<string>();
                //foreach (SelectColumn f in (query as QuerySpecification).SelectElements)
                //{
                //    if( f.ColumnName != null)
                //    {
                //        //// TODO Find ColumnName 
                //        fieldList.Add( f.ColumnName.ToString());
                //    }
                //}
                //string fields = string.Join(", ", fieldList.ToArray());

                if (query is QuerySpecification)
                {
                    Boolean erg = this.queryViewCheck(type, name, fileName, (query as QuerySpecification).FromClauses);
                    if (isFault)
                        isFault = erg;
                }
                else if (query is BinaryQueryExpression)
                {
                    Boolean erg1 = this.queryViewCheck(type, name, fileName, ((query as BinaryQueryExpression).FirstQueryExpression as QuerySpecification).FromClauses);
                    Boolean erg2 = this.queryViewCheck(type, name, fileName, ((query as BinaryQueryExpression).SecondQueryExpression as QuerySpecification).FromClauses);

                    if (isFault && (!erg1 || !erg2))
                        isFault = false;
                }
                else
                {
                    MessageBox.Show("'" + query.GetType().ToString() + "' is not implemented", this.Name, MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }

            return isFault;
        }

        /// <summary>
        ///  Search for a View in the database select. The query is evaluated.
        ///  
        ///  TODO TODO TODO TODO Installing extra treatment for Joins
        /// 
        /// </summary>
        /// <param name="type">Type of component</param>
        /// <param name="name">Name of component</param>
        /// <param name="fileName">File name</param>
        /// <param name="list">List with TabSources</param>
        private Boolean queryViewCheck(string type, string name, string fileName, IList<TableSource> list)
        {
            Boolean result = true;
            // Constructs the From clause with the optional joins
            foreach (TableSource tabSource in list)
            {
                // Joins raise exeptions
                if (tabSource.GetType().Name == "SchemaObjectTableSource")
                {
                    string from = (tabSource as SchemaObjectTableSource).SchemaObject.BaseIdentifier.Value;

                    if (!from.ToLower().Contains("vw_"))
                    {
                        this.resultArrayList.AddRange(new object[] { new object[] { type, name, "Error: Please use only Views in Select Statements '" + from + "'", fileName } });

                        if (result) result = false;
                    }
                }
            }
            return result;
        }

        /// <summary>
        ///  Search for column names in the Select Database
        /// </summary>
        /// <param name="element">XElement to be tested</param>
        /// <param name="fileName">File name</param>
        private Boolean checkColumnUse(XElement element, string fileName)
        {
            Boolean isFault = true;
            string type = element.Attribute("contactInfo").Value.Split(new Char[] { ';' }).First();
            string componentClassID = element.Attribute("componentClassID").Value.ToString();
            string name = element.Attribute("name").Value;

            string sqlCommand = "";

            IEnumerable<XElement> SQLCommand = from f in element.Elements("properties").Elements()
                                               where (string)f.Attribute("name").Value.ToLower() == "SqlCommand".ToLower()
                                               select f;

            foreach (XElement el in SQLCommand)
            {
                sqlCommand = el.Value.ToString();
            }

            if (System.Text.RegularExpressions.Regex.IsMatch(sqlCommand.ToLower(), "select \\*", System.Text.RegularExpressions.RegexOptions.IgnoreCase))
            {
                resultArrayList.AddRange(new object[] { new object[] { type, name, "Error: Do not use 'Select *' - Your Command: " + sqlCommand, fileName } });
                isFault = true;
            }
            else
                isFault = false;
            
            return !isFault;
        }

        /// <summary>
        ///  Use of fast loading
        /// </summary>
        /// <param name="element">XElement to be tested</param>
        /// <param name="fileName">File name</param>
        private Boolean checkOleDbDestination(XElement element, string fileName)
        {
            Boolean isFault = true;
            string type = element.Attribute("contactInfo").Value.Split(new Char[] { ';' }).First();
            string componentClassID = element.Attribute("componentClassID").Value.ToString();
            string name = element.Attribute("name").Value;

            string fastLoadOptions = "";

            IEnumerable<XElement> FastLoadOptions = from f in element.Elements("properties").Elements()
                                               where (string)f.Attribute("name") == "FastLoadOptions"
                                           select f;

            foreach (XElement el in FastLoadOptions)
            {
                fastLoadOptions = el.Value.ToString();
            }


            if (fastLoadOptions == "")
                isFault = true;
            else
                isFault = false;

            if (isFault)
                resultArrayList.AddRange(new object[] { new object[] { type, name, "Error: You have not used fast loading", fileName } });

            return !isFault;
        }

        /// <summary>
        ///  Verification of compliance with the naming convention
        /// </summary>
        /// <param name="type">Type of component</param>
        /// <param name="name">Name of component</param>
        /// <param name="fileName">File name</param>
        private void checkComponent(string type, string name, string fileName, string componentClassID)
        {
            Boolean isFault = true;
            string prefix = "";

            if (this.conventionDictEnglish.TryGetValue(type, out prefix))
                isFault = !name.Contains(prefix);
            else if (this.conventionDictGerman.TryGetValue(type, out prefix))
                isFault = !name.Contains(prefix);

            if( isFault )
                resultArrayList.AddRange(new object[] { new object[] { type, name, "The naming convention has not been complied (" + prefix + ")", fileName } });
        }

        /// <summary>
        ///  Write analysis result into a log file
        /// </summary>
        private void button3_Click(object sender, EventArgs e)
        {
            this.saveFileDialog1.Filter = "csv-File|*.csv";
            this.saveFileDialog1.FileName = "report_" + DateTime.Now.ToShortDateString().Replace(".", "") + "_" + this.fileNr + ".csv";

            DialogResult result = this.saveFileDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                string filePath = this.saveFileDialog1.FileName;
                try
                {
                    string t = ";";
                    string fileOutput = "component type;component name;error message;dtsx file\r\n";
                    
                    StreamWriter newFile = new StreamWriter(filePath);
                    newFile.Write(fileOutput);

                    foreach (Object[] erg in this.resultArrayList)
                    {
                        fileOutput = (String)erg[0] + t + (String)erg[1] + t + (String)erg[2] + t + (String)erg[3] + "\r\n";
                        newFile.Write(fileOutput);
                    }
                    newFile.Close();

                }
                catch (Exception ex)
                {
                    MessageBox.Show("Failed to create the file '" + filePath + "':\n" + ex.Message, this.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

                this.fileNr++;


                if (MessageBox.Show("Would you mind opening the logfile? ", this.Name,
                    MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    Process p = new Process();
                    p.StartInfo.FileName = filePath;
                    p.Start();
                }
            }
        }
    }
}
